﻿using System;
using System.Reflection;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;


namespace cz.uhul.DSMLv2.Server.Sql
{
  public partial class LdDatabase
  {

    private static SqlConnection oConn;




    /// <summary>
    /// gives information about version of L2DB database
    /// </summary>
    public struct Version
    { 
      /// <summary>
      /// major db version number, most significant value
      /// </summary>
      public int Major;

      /// <summary>
      /// minor db version number, middle value
      /// </summary>
      public int Minor;

      /// <summary>
      /// programatic version of db, less significant value
      /// </summary>
      public int Program;


      public override string ToString()
      {
        return this.Major.ToString() + "." + this.Minor.ToString() + "." + this.Program.ToString();
      }
    }




    static LdDatabase()
    {
      //vyhodit vyjimku kdyz nebude nalezen connection string
      //vyhodit vyjimku pokud se nepodari spojeni s databazi

      string sConnStr = ConfigurationManager.ConnectionStrings["L2DB"].ConnectionString;
      //string sConnStr = "Data Source=(local); Initial Catalog=DS_LHPO_WORK; Integrated Security=True; MultipleActiveResultSets=True";

      //oConn = new SqlConnection(sConnStr);
      //oConn.Open();

      //root DSE is stored as appSetting in web.config or app.config
      RootDSE = ConfigurationManager.AppSettings["RootDSE"];

    }



    /// <summary>
    /// probes given configuration against backend database. This should be called before
    /// any higher level service tries to access db.
    /// Probe tries to connect do db and checks L2DB version compatibility
    /// </summary>
    public static void Probe()
    {
      _captureDbConn();

      SqlCommand cmd;

      try
      {
        cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "dbo.sp_GetDbVer";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@MAJOR"].Value = DBNull.Value;
        cmd.Parameters["@MINOR"].Value = DBNull.Value;
        cmd.Parameters["@PROG"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();

        Version dbver = new Version()
        {
          Major = (int)cmd.Parameters["@MAJOR"].Value
          ,Minor = (int)cmd.Parameters["@MINOR"].Value
          ,Program = (int)cmd.Parameters["@PROG"].Value
        };

        System.Version aVer = Assembly.GetExecutingAssembly().GetName().Version;

        if (
          aVer.Major != dbver.Major
          || aVer.Minor != dbver.Minor
          || aVer.Build < dbver.Program
        )
          throw new InvalidProgramException("db version:" + dbver.ToString() + ", is not compatible with current assembly:" + aVer.Major.ToString() + "." + aVer.Minor.ToString() + "." + aVer.Build.ToString());

      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }





    private static bool _captureDbConn()
    {
      //traceTms("_captureDbConn()");

      try
      {
        oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["L2DB"].ConnectionString);
        oConn.Open();
        return true;
      }
      catch (Exception e)
      {
        //traceTms("_captureDbConn(), nezdarilo se pripojeni na db " + e.Message);
        //writeEventLogErr(e, "_captureDbConn(), db problem");
        return false;
      }
    }






    private static void _releaseDbConn()
    {
      //traceTms("_releaseDbConn()");

      try { oConn.Close(); }
      catch { }
    }





    public static bool AddEntry(string distinguishedName, string superiorClass, string relativeDistinguishedName, string superiorPath, bool isInstance)
    {
      _captureDbConn();

      SqlCommand cmd;

      try
      {
        cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_AddEntry";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@SuperClass"].Value = superiorClass;
        cmd.Parameters["@RelativeDistinguishedName"].Value = relativeDistinguishedName;
        cmd.Parameters["@SuperPath"].Value = superiorPath;
        cmd.Parameters["@IsInstance"].Value = (isInstance == true) ? SqlBoolean.True : SqlBoolean.False;
        cmd.Parameters["@IsDirty"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();

        return !(bool)cmd.Parameters["@IsDirty"].Value;
      }
      catch(Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
     

    }




    public static void DeleteEntry(string distinguishedName)
    {
      _captureDbConn();

      try
      {

        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_DeleteEntry";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@DnKey"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static void ModifyDn(string distinguishedName, string newDistinguishedName, string newRDN, string newSuperiorPath, bool deleteOldRDN)
    {
      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_ModifyDn";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@NewDistinguishedName"].Value = newDistinguishedName;
        cmd.Parameters["@NewRelativeDistinguishedName"].Value = newRDN;
        cmd.Parameters["@NewSuperPath"].Value = newSuperiorPath;
        cmd.Parameters["@DeleteOld"].Value = (deleteOldRDN == true) ? SqlBoolean.True : SqlBoolean.False;
        cmd.Parameters["@DnKey"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static void DeleteTree(string distinguishedName)
    {
      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_DeleteTree";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static bool AddAttrValue(string distinguishedName, string attributeName, string AttrValue)
    {

      if (AttrValue == null)
        return false; //cant save null

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_AddRelation";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;
        cmd.Parameters["@Value"].Value = AttrValue;
        cmd.Parameters["@IsDirty"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.ExecuteNonQuery();

        _releaseDbConn();

        return !(bool)cmd.Parameters["@IsDirty"].Value;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static bool AddAttrValues(string distinguishedName, string attributeName, ObservableCollection<string> AttrValues)
    {

      if (AttrValues.Count == 0)
        return true; //no values to add = valid

      if (AttrValues.Count < 2)
        return AddAttrValue(distinguishedName, attributeName, AttrValues[0]);

      try
      {
        //try add first 2 values safe way
        //this will eliminate single-value error + test other constraints
        bool IsValid;
        IsValid = AddAttrValue(distinguishedName, attributeName, AttrValues[0]);
        IsValid |= AddAttrValue(distinguishedName, attributeName, AttrValues[1]);

        //get entry and attr info...
        LdRequestServerExtension.EntryNfo ent = GetEntryNfo(distinguishedName, Guid.Empty); //fake guid, not used here
        LdRequestServerExtension.AttrNfo attr = GetAttrNfo(distinguishedName, attributeName);

        _captureDbConn();

        if (
          ent.IsInstance
          && attr.IsInstance
          && !(attr.Syntax == "1.3.6.1.4.1.1466.115.121.1.12" || String.IsNullOrEmpty(attr.Syntax))
        )
        {
          //and rest process as bulk
          //this is quite faster than ld.sp_AddRelation
          SqlCommand cmd = oConn.CreateCommand();
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "ldsys.sp_AddInstanceValueRelationBulk";
          SqlCommandBuilder.DeriveParameters(cmd);
          //cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
          cmd.Parameters["@DnKey"].Value = ent.DnKey;
          cmd.Parameters["@AttrDnKey"].Value = attr.AttrDnKey;
          cmd.Parameters["@SYNTAX"].Value = attr.Syntax;

          cmd.CommandTimeout = 30; //seconds max.

          for (int i = 2; i < AttrValues.Count; i++)
            if (AttrValues[i] != null) //save only not null values
            {
              cmd.Parameters["@Value"].Value = AttrValues[i];
              cmd.ExecuteNonQuery();
            }
        }
        else
        {
          //and rest process as bulk
          //this is quite faster than ld.sp_AddRelation
          _captureDbConn();

          SqlCommand cmd = oConn.CreateCommand();
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "ld.sp_AddRelationBulk";
          SqlCommandBuilder.DeriveParameters(cmd);
          cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
          cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
          cmd.Parameters["@AttributeName"].Value = attributeName;
          //cmd.Parameters["@IsDirty"].Value = DBNull.Value;

          cmd.CommandTimeout = 30; //seconds max.

          for (int i = 2; i < AttrValues.Count; i++)
            if (AttrValues[i] != null) //save only not null values
            {
              cmd.Parameters["@Value"].Value = AttrValues[i];
              cmd.ExecuteNonQuery();
            }
        }

        _releaseDbConn();

        //return !(bool)cmd.Parameters["@IsDirty"].Value;
        return !IsValid;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static void ReplaceSingleAttrValue(string distinguishedName, string attributeName, string newAttrValue)
    {

      if (newAttrValue == null)
        return; //null cant save

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_ReplaceRelation";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;
        cmd.Parameters["@OldValue"].Value = DBNull.Value;
        cmd.Parameters["@NewValue"].Value = newAttrValue;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }






    public static void ReplaceMultiAttrValue(string distinguishedName, string attributeName, string oldAttrValue, string newAttrValue)
    {

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_ReplaceRelation";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;
        cmd.Parameters["@OldValue"].Value = oldAttrValue;
        cmd.Parameters["@NewValue"].Value = newAttrValue;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        _releaseDbConn();
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }




    public static bool DeleteAllAttrValue(string distinguishedName, string attributeName)
    {

      bool result;

      _captureDbConn();

      SqlCommand cmd = null;

      try
      {
        cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_DeleteRelations";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;
        cmd.Parameters["@IsDirty"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.Transaction = oConn.BeginTransaction("deleteAllAttributeValue");

        //return cmd.ExecuteReader();
        cmd.ExecuteNonQuery();

        result = (bool)cmd.Parameters["@IsDirty"].Value;

        if (result)
          cmd.Transaction.Rollback(); //is dirty = rollback

        else
          cmd.Transaction.Commit();

        _releaseDbConn();

        return !result;

      }
      catch (Exception e)
      {
        if (cmd.Transaction != null)
          cmd.Transaction.Rollback();

        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static bool DeleteAttrValue(string distinguishedName, string attributeName, string attrValue)
    {

      if (attrValue == null)
        return false;

      bool result;

      _captureDbConn();

      SqlCommand cmd = null;

      try
      {
        cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_DeleteRelation";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;
        cmd.Parameters["@Value"].Value = attrValue;
        cmd.Parameters["@IsDirty"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        //td = oConn.BeginTransaction("deleteAttributeValue");

        cmd.Transaction = oConn.BeginTransaction("deleteAttributeValue");
        cmd.ExecuteNonQuery();

        result = (bool)cmd.Parameters["@IsDirty"].Value;

        if (result)
          cmd.Transaction.Rollback(); //is dirty = rollback
        
        else
          cmd.Transaction.Commit();

        _releaseDbConn();
        return !result;
      }
      catch (Exception e)
      {
        if (cmd.Transaction != null)
          cmd.Transaction.Rollback();

        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }





    public static bool Compare(string distinguishedName, string attributeNameAssert, string attrValueAssert)
    {

      //bool result;

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_Compare";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeNameAssert;
        cmd.Parameters["@Value"].Value = attrValueAssert;
        cmd.Parameters["@CompareResult"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.ExecuteNonQuery();

        _releaseDbConn();

        return (bool)cmd.Parameters["@CompareResult"].Value;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
      //return result;
    }




    public static LdRequestServerExtension.AttrNfo GetAttrNfo(string distinguishedName, string attrName)
    {

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_GetAttributeNfo";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttrName"].Value = attrName;
        cmd.Parameters["@AttrDnKey"].Value = DBNull.Value;
        cmd.Parameters["@DefiningClassKey"].Value = DBNull.Value;
        cmd.Parameters["@IsInstanceAttribute"].Value = DBNull.Value;
        cmd.Parameters["@IsSingleValue"].Value = DBNull.Value;
        cmd.Parameters["@IsMust"].Value = DBNull.Value;
        cmd.Parameters["@SYNTAX"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.ExecuteNonQuery();

        _releaseDbConn();

        LdRequestServerExtension.AttrNfo tmp = new LdRequestServerExtension.AttrNfo();

        tmp.AttrDnKey = _getGuid(cmd.Parameters["@AttrDnKey"].Value);
        tmp.DefiningClassKey = _getGuid(cmd.Parameters["@DefiningClassKey"].Value);
        tmp.IsInstance = (bool)cmd.Parameters["@IsInstanceAttribute"].Value;
        tmp.IsSingleValue = (bool)cmd.Parameters["@IsSingleValue"].Value;
        tmp.IsMust = (bool)cmd.Parameters["@IsMust"].Value;
        tmp.Syntax = (string)cmd.Parameters["@SYNTAX"].Value;

        return tmp;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static LdRequestServerExtension.EntryNfo GetEntryNfo(string distinguishedName, Guid dnKey)
    {

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_GetEntryNfo";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;

        if (!String.IsNullOrEmpty(distinguishedName))
        {
          cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
          cmd.Parameters["@DnKey"].Value = DBNull.Value;
        }
        else
        {
          cmd.Parameters["@DistinguishedName"].Value = DBNull.Value;
          cmd.Parameters["@DnKey"].Value = new SqlGuid(dnKey);
        }

        cmd.Parameters["@SuperPath"].Value = DBNull.Value;
        cmd.Parameters["@RelativeDistinguishedName"].Value = DBNull.Value;
        cmd.Parameters["@SuperClassKey"].Value = DBNull.Value;
        cmd.Parameters["@IsInstance"].Value = DBNull.Value;
        cmd.Parameters["@IsDirty"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.ExecuteNonQuery();

        _releaseDbConn();

        LdRequestServerExtension.EntryNfo tmp = new LdRequestServerExtension.EntryNfo();

        tmp.IsDirty = (bool)cmd.Parameters["@IsDirty"].Value;
        tmp.IsInstance = (bool)cmd.Parameters["@IsInstance"].Value;
        tmp.ClassDnKey = _getGuid(cmd.Parameters["@SuperClassKey"].Value);
        tmp.DN = (string)cmd.Parameters["@DistinguishedName"].Value;
        tmp.DnKey = _getGuid(cmd.Parameters["@DnKey"].Value);

        return tmp;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }

    }





    public static List<Guid> Search(
      string scope
      ,string assertionName
      ,string distinguishedName
      ,string attributeName
      ,string attributeValue
      ,string substringAssertion
      ,bool negate
    )
    {

      _captureDbConn();

      string procName = "ld.sp_Search_" + scope + "_" + assertionName;

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = procName;
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;

        if(assertionName.ToLower() == "equalitymatch")
          cmd.Parameters["@Value"].Value = attributeValue;

        if (assertionName.ToLower() == "substring")
        {
          cmd.Parameters["@Value"].Value = attributeValue;
          cmd.Parameters["@Assertion"].Value = substringAssertion;
        }

        cmd.Parameters["@negateOp"].Value = (negate == true) ? SqlBoolean.True : SqlBoolean.False;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        SqlDataReader oDR = cmd.ExecuteReader();

        List<Guid> res = new List<Guid>();

        while (oDR.Read())
        {
          res.Add((Guid)oDR["DnKey"]);
        }
        oDR.Close();

        _releaseDbConn();

        return res;

      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }





    public static List<Guid> GetEntriesByDn(
      string distinguishedName
      ,bool negate
    )
    {

      _captureDbConn();

      string procName = "ld.sp_GetEntriesByDN";

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = procName;
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DNlike"].Value = distinguishedName;
        cmd.Parameters["@negateOp"].Value = (negate == true) ? SqlBoolean.True : SqlBoolean.False;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        SqlDataReader oDR = cmd.ExecuteReader();

        List<Guid> res = new List<Guid>();

        while (oDR.Read())
        {
          res.Add((Guid)oDR["DnKey"]);
        }
        oDR.Close();

        _releaseDbConn();

        return res;

      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }

    



    public static List<string> GetAttributeNames(string distinguishedName)
    {
      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_GetAttributes";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        SqlDataReader oDR = cmd.ExecuteReader();

        List<string> res = new List<string>();

        while (oDR.Read())
        {
          res.Add((string)oDR["RDN"]);
        }
        oDR.Close();

        _releaseDbConn();

        return res;

      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }





    public static List<string> GetRelations(string distinguishedName, string attributeName)
    {
      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_GetRelations";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@AttributeName"].Value = attributeName;

        cmd.CommandTimeout = 30; //seconds max.

        //return cmd.ExecuteReader();
        SqlDataReader oDR = cmd.ExecuteReader();

        List<string> res = new List<string>();

        while (oDR.Read())
        {
          res.Add((string)oDR["value"]);
        }
        oDR.Close();

        _releaseDbConn();

        return res;

      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
    }






    public static string GetClassModifier(string distinguishedName)
    {

      //bool result;

      _captureDbConn();

      try
      {
        SqlCommand cmd = oConn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ld.sp_GetModifierN";
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters["@RootDSE"].Value = LdDatabase.RootDSE;
        cmd.Parameters["@DistinguishedName"].Value = distinguishedName;
        cmd.Parameters["@Modifier"].Value = DBNull.Value;

        cmd.CommandTimeout = 30; //seconds max.

        cmd.ExecuteNonQuery();

        _releaseDbConn();

        return (string)cmd.Parameters["@Modifier"].Value;
      }
      catch (Exception e)
      {
        _releaseDbConn();
        throw new LdException(e.Message);
        //return false;
      }
      //return result;
    }





    /// <summary>
    /// get ccurrent RootDSE DIT name, preconfigured in web.config.
    /// DSML service cant select various RootDSE as protocol is build to work with predefined one.
    /// </summary>
    public static string RootDSE { get; set; }
    


  }
}